package edu.xhu.util;


import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.bean.StuCourseBean;
import com.bean.TeachingBean;
import com.bean.User;

import edu.xhu.XhuApplication;

public class DatabaseAdapter {

	/**
	 * 数据库名字
	 */
	public static final String DATABASE_NAME = "myDatabase.db";
	/**
	 * 课程表的表名
	 */
	public static final String COURSE_TABLE = "course";
	/**
	 * 学生表的表名
	 */
	public static final String STUDENT_TABLE = "student";

	//课程表的字段名
	public static final String ID				= "id";
	public static final String COURSE_NAME		= "course_name";
	
	public static final String STUDENT_NUMBER 	= "student_number";					//学号
	public static final String STUDENT_NAME		= "student_name";					//姓名
	public static final String STUDENT_COURSE	= "student_course";					//该学生所上的课
	public static final String STUDENT_REMARK	= "student_remark";					//对该学生的备注信息
	public static final String STUDENT_MAJOR	= "major";							//专业
	public static final String STUDENT_CLASSES	= "classes";						//班级
	public static final String STUDENT_GRADES	= "grades";							//成绩
	public static final String STUDENT_CREDIT	= "credit";							//学分
	public static final String STUDENT_REBUILD	= "rebuild";						//重修标记
	
	/**
	 * 教务系统用户表的表名
	 */
	public static final String USER_TABLE		= "user";
	public static final String USER_ID			= "id";								//用户登录的帐号
	public static final String USER_PWD			= "password";						//用户登录的密码
	public static final String USER_ROLE		= "role";							//用户的角色
	public static final String USER_NAME		= "name";							//用户的名字
	/**
	 * 图书馆用户表的表名
	 */
	public static final String LIBRARY_TABLE	= "library";						//图书馆账户表名
	/**
	 * 软件主题信息
	 */
	public static final String SOFTWARE_THEME	= "software_theme";					//表名字				
	public static final String THEME			= "theme";							//用户选择的软件主题样式，字段名字
	
	/**
	 * 课表表名和字段名
	 */
	public static final String TEACHER_TIME_TABLE	= "teacher_time_table";			//教师课表名
	public static final String STUDENT_TIME_TABLE	= "student_time_table";			//学生课表名
	
	public static final String COURSE_NAME1			= "course_name";				//课程名称
	public static final String PLACE				= "place";						//上课地点
	public static final String TEACHER				= "teacher";					//上课教师
	public static final String TIME					= "time";						//上课时间
	public static final String REMARK				= "remark";						//备注信息
	public static final String MORE					= "more";						//更多信息-针对教师
	
	
	
	//----------------------------------------------------------------------------------------------------------------
	private DatabaseHelper dbHelper;
	private SQLiteDatabase db;
	private Cursor cursor;
	private Context context;
	
	public DatabaseAdapter(Context context){
		this.context = context;
		dbHelper = new DatabaseHelper(context, DATABASE_NAME, null, 2);
	}
	
	/**
	 * 打开数据库
	 */
	public void openDatabase(){
		try {
			
			db = dbHelper.getWritableDatabase();
			
			dbHelper.createTable(db);
			
		} catch (SQLException e) {
			db = dbHelper.getReadableDatabase();
		}
	}
	
	/**
	 * 关闭数据库
	 */
	public void closeDatabase(){
		db.close();
	}

	/**
	 * 添加一个学生
	 * @param id:唯一id
	 * @param stuNum：学号
	 * @param name：姓名
	 * @param major：专业
	 * @param classes：班级
	 * @param grades：成绩
	 * @param credit：学分
	 * @param rebuild：重修标记
	 * @param course：所选课程
	 * @param remark：备注信息
	 * @return
	 */
	public long addOneStudent(long id, String stuNum, String name, String major, String classes
			, String grades, String credit, String rebuild, String course, String remark){
		
		ContentValues contentValues = new ContentValues();
		contentValues.put(ID, id);
		contentValues.put(STUDENT_NUMBER, stuNum);
		contentValues.put(STUDENT_NAME, name);
		contentValues.put(STUDENT_MAJOR, major);
		contentValues.put(STUDENT_CLASSES, classes);
		contentValues.put(STUDENT_GRADES, grades);
		contentValues.put(STUDENT_CREDIT, credit);
		contentValues.put(STUDENT_REBUILD, rebuild);
		contentValues.put(STUDENT_COURSE, course);
		contentValues.put(STUDENT_REMARK, remark);
		
		return db.insert(STUDENT_TABLE, null, contentValues);
	}
	
	/**
	 * 获取第一次插入学生表，在表中的位置
	 * @return
	 */
	public long getNextIndex(){
		String sql = "SELECT " + ID + " FROM " + STUDENT_TABLE;
		cursor = db.rawQuery(sql, null);
		if (cursor.getCount() == 0){
			cursor.close();
			return 0;
		}
		
		cursor.moveToLast();
		long n = cursor.getInt(0);
		
		cursor.close();
		
		return ++n;
	}
	
//	public void printTable(){
//		String sql = "SELECT * FROM " + STUDENT_TABLE;
//		cursor = db.rawQuery(sql, null);
//		
//		cursor.moveToFirst();
//		while (!cursor.isAfterLast()){
//			int id = cursor.getInt(0);
//			String num = cursor.getString(1);
//			String name = cursor.getString(2);
//			String major = cursor.getString(3);
//			String classes = cursor.getString(4);
//			String grades = cursor.getString(5);
//			String credit = cursor.getString(6);
//			String rebuild = cursor.getString(7);
//			String course = cursor.getString(8);
//			String remark = cursor.getString(9);
//			
//			System.out.println("id=" + id + ";num=" + num + ";name" + name + ";major=" + major
//					+ ";classes=" + classes + ";grades=" + grades + ";credit=" + credit
//					+ ";rebuild=" + rebuild + ";course=" + course + ";remark=" + remark);
//			
//			cursor.moveToNext();
//		}
//	}
	
	/**
	 * 删除所有的数据，包括整个表
	 */
	public void deleteAll(){
		//删除教师上课的学生名单列表
		String sql = "DROP TABLE IF EXISTS " + STUDENT_TABLE;
		db.execSQL(sql);
		//删除课程名称列表
		sql = "DROP TABLE IF EXISTS " + COURSE_TABLE;
		db.execSQL(sql);
		//删除教务系统用户表
		sql = "DROP TABLE IF EXISTS " + USER_TABLE;
		db.execSQL(sql);
		//删除软件主题信息表
		sql = "DROP TABLE IF EXISTS " + SOFTWARE_THEME;
		db.execSQL(sql);
		//删除图书馆帐号信息表
		sql = "DROP TABLE IF EXISTS " + LIBRARY_TABLE;
		db.execSQL(sql);
		//删除教师课程表
		sql = "DROP TABLE IF EXISTS " + TEACHER_TIME_TABLE;
		db.execSQL(sql);
		//删除学生课程表
		sql = "DROP TABLE IF EXISTS " + STUDENT_TIME_TABLE;
		db.execSQL(sql);
	}
	
	/**
	 * 根据某一课程查询班上的学生
	 * @param courseName
	 * @return
	 */
	public ArrayList<TeachingBean> getOneCourseStudents(String courseName){
		String sql = "SELECT * FROM " + STUDENT_TABLE + " WHERE " + STUDENT_COURSE + "=\"" + courseName + "\";";
		cursor = db.rawQuery(sql, null);
		
		if (cursor.getCount() == 0){
			cursor.close();
			return null;
		}
		
		ArrayList<TeachingBean> list = new ArrayList<TeachingBean>();
		
		cursor.moveToFirst();
		while (!cursor.isAfterLast()){
			TeachingBean bean = new TeachingBean();
			bean.number = cursor.getString(1);
			bean.name = cursor.getString(2);
			bean.professionName = cursor.getString(3);
			bean.clsName = cursor.getString(4);
			bean.grade = cursor.getString(5);
			bean.coursecredit = cursor.getString(6);
			bean.rebulid = cursor.getString(7);
			bean.remark = cursor.getString(9);
			
			list.add(bean);
			cursor.moveToNext();
		}
		
		cursor.close();
		
		return list;
	}
	
	/**
	 * 取得科目表中下一个可以插入的位置
	 * @return
	 */
	public long getNextCourseIndex(){
		String sql = "SELECT COUNT(*) FROM " + COURSE_TABLE;
		cursor = db.rawQuery(sql, null);
		cursor.moveToFirst();//这是不是应该是.moveToLast()
		long n = cursor.getInt(0);
		
		cursor.close();
		
		return n;
	}
	
	/**
	 * 保存一科目信息
	 * @param id
	 * @param courseName
	 * @return 返回当前插入的位置
	 */
	public long saveCourses(long id, String courseName){
		ContentValues contentValues = new ContentValues();
		contentValues.put(ID, id);
		contentValues.put(COURSE_NAME, courseName);
		/**
		 * 加入判断是否有相同id的判断
		 */
		return db.insert(COURSE_TABLE, null, contentValues);
	}
	
	/**
	 * 获取数据库中所有的课表名信息
	 * @return
	 */
	public ArrayList<String> getCourseNameList(){
		String sql = "SELECT " + COURSE_NAME +" FROM " + COURSE_TABLE;
		cursor = db.rawQuery(sql, null);
		
		ArrayList<String> classesName = new ArrayList<String>();
		cursor.moveToFirst();
		while (!cursor.isAfterLast()){
			String name = cursor.getString(0);
			if (!name.equals("0"))
				classesName.add(name);
			
			cursor.moveToNext();
		}
		
		cursor.close();
		
		return classesName;
	}
	
	/**
	 * 判断课程表目录列表是否可以插入数据
	 * @return：true：可以插入数据
	 */
	public boolean isCourseListEnable(){
		String sql = "SELECT " + COURSE_NAME + " FROM " + COURSE_TABLE;
		cursor = db.rawQuery(sql, null);
		cursor.moveToFirst();
		String s = "";
		while (!cursor.isAfterLast()){
			s = cursor.getString(0);
			cursor.moveToNext();
		}
		
		cursor.close();
		
		if (s.equals("0")){
			return false;
		}else {
			return true;
		}
	}
	
//	public void printCourse(){
//		String sql = "SELECT * FROM " + COURSE_TABLE;
//		cursor = db.rawQuery(sql, null);
//		
//		cursor.moveToFirst();
//		while (!cursor.isAfterLast()){
//			int id = cursor.getInt(0);
//			String name = cursor.getString(1);
//			
////			System.out.println("id=" + id + ";name" + name);
//			
//			cursor.moveToNext();
//		}
//	}
	
	/**
	 * 删除一门课的所有学生名单
	 * @param courseName
	 */
	public void deleteOneCourseStuList(String courseName){
		String sql = "DELETE FROM " + STUDENT_TABLE + " WHERE " + STUDENT_COURSE + "=\"" + courseName + "\";";
		db.execSQL(sql);
	}
	
	/**
	 * 保存一个学生的备注信息
	 * @param stuName:学生的名字
	 * @param courseName：学生上的课名字
	 */
	public void saveRemark(String stuName, String courseName, String remark){
		String sql = "UPDATE " + STUDENT_TABLE + " SET " + STUDENT_REMARK + "=\"" + remark + "\" WHERE "
				+ STUDENT_NAME + "=\"" + stuName + "\" AND " + STUDENT_COURSE + "=\"" + courseName + "\";";
		
		db.execSQL(sql);
	}
	
	/**
	 * 保存一个用户的账户信息，可能是图书馆的账户，可能是教务系统的账户
	 * @param user
	 */
	public long saveAccount(User user){
		long result = -1;
		ContentValues contentValues = new ContentValues();
		String nid = "1" + user.getUserId();
		String pwd = "1" + user.getPassword();
		//进行加密
		nid = secret.encodeToString(nid.getBytes(), 0);
		pwd = secret.encodeToString(pwd.getBytes(), 0);
		
		contentValues.put(USER_ID, nid);
		contentValues.put(USER_PWD, pwd);
		
		if (user.getPart().equals(User.LIBRARY)) {
			// 将信息插入到图书馆表中
			if (!isExists(LIBRARY_TABLE, USER_ID, nid))
				result = db.insert(LIBRARY_TABLE, null, contentValues);

		} else if (user.getPart().equals(User.EDU_SYS)) {
			// 将信息插入到教务系统的表中
			contentValues.put(USER_ROLE, user.getRole());
			contentValues.put(USER_NAME, user.getName());
			
			if (!isExists(USER_TABLE, USER_ID, nid)){
				/**
				 * 此处以0开头的帐号会将0略去，故不好测试
				 */
				result = db.insert(USER_TABLE, null, contentValues);
			}
		}
		
		return result;
	}
	
	/**
	 * 获取账户信息
	 * @param part：LIBRARY-图书馆的账户；EDU_SYS-教务系统的账户
	 */
	public User getAccount(String part){
		User user = new User();
		String sql = "";
		
		if (part.equals(User.LIBRARY)){
			sql = "SELECT * FROM " + LIBRARY_TABLE + ";";
			cursor = db.rawQuery(sql, null);
			cursor.moveToFirst();
			if (!cursor.isAfterLast()){
				String id = cursor.getString(0);
				id = new String(secret.decode(id, 0));
				id = id.substring(1, id.length());
				user.setUserId(id);
				
				String pwd = cursor.getString(1);
				pwd = new String(secret.decode(pwd, 0));
				pwd = pwd.substring(1, pwd.length());
				user.setPassword(pwd);
				
//				String id = cursor.getString(0);
//				user.setUserId(id.substring(1, id.length()));
//				String pwd = cursor.getString(1);
//				user.setPassword(pwd.substring(1, pwd.length()));
			}
			cursor.close();
			return user;
			
		}else if (part.equals(User.EDU_SYS)) {
			sql = "SELECT * FROM " + USER_TABLE + ";";
			cursor = db.rawQuery(sql, null);
			cursor.moveToFirst();
			if (!cursor.isAfterLast()){
				String id = cursor.getString(0);
				id = new String(secret.decode(id, 0));
				id = id.substring(1, id.length());
				user.setUserId(id);
				
				String pwd = cursor.getString(1);
				pwd = new String(secret.decode(pwd, 0));
				pwd = pwd.substring(1, pwd.length());
				user.setPassword(pwd);
				
//				String id = cursor.getString(0);
//				user.setUserId(id.substring(1, id.length()));
//				String pwd = cursor.getString(1);
//				user.setPassword(pwd.substring(1, pwd.length()));
				
				user.setRole(cursor.getString(2));
				user.setName(cursor.getString(3));
			}
			cursor.close();
			return user;
		}
		
		cursor.close();
		return null;
	}
	
	/**
	 * 判断一个表中的其中一个字段的值是否存在
	 * @param tableName
	 * @param filed
	 * @param value
	 * @return:true-存在该值，false-不存在该值
	 */
	private boolean isExists(String tableName, String filed, String value){
		String sql = "SELECT " + filed + " FROM " + tableName + ";";
		cursor = db.rawQuery(sql, null);
		cursor.moveToFirst();
		
		while (!cursor.isAfterLast()){
			String rs = cursor.getString(0);
			if (value.equals(rs)){
				cursor.close();
				return true;
			}
			
			cursor.moveToNext();
		}
		
		cursor.close();
		return false;
	}
	
	/**
	 * 添加课表一堂课的信息
	 * @param role：该课表信息的角色-教师还是学生
	 * @return：不为-1则插入成功
	 */
	public long saveOneTimeTable(int role, StuCourseBean stuCourseBean){
		long result = -1;
		String id, courseName, teacher, place, time, remark, more;
		
		id = "1" + stuCourseBean.x + stuCourseBean.y;   //因为系统会将字符串前的0给自动删掉，目前不清楚原因，所以先在字符串前面加上1
		courseName = stuCourseBean.name;
		teacher = stuCourseBean.teacher;
		place = stuCourseBean.site;
		time = stuCourseBean.time;
		remark = stuCourseBean.remark;
		more = stuCourseBean.more;
		
		ContentValues contentValues = new ContentValues();
		contentValues.put(ID, id);
		contentValues.put(COURSE_NAME1, courseName);
		contentValues.put(TEACHER, teacher);
		contentValues.put(PLACE, place);
		contentValues.put(TIME, time);
		contentValues.put(REMARK, remark);
		
		if (role == XhuApplication.TEACHER){
			//处理教师课表
			contentValues.put(MORE, more);
			//在插入数据之前，先删掉原来的数据
			deleteOne(TEACHER_TIME_TABLE, id);
			result = db.insert(TEACHER_TIME_TABLE, null, contentValues);
			
		}else if (role == XhuApplication.STUDENT) {
			//处理学生课表
			deleteOne(STUDENT_TIME_TABLE, id);
			result = db.insert(STUDENT_TIME_TABLE, null, contentValues);
		}
		
		return result;
	}
	
	/**
	 * 删除表中的一行数据
	 * @param table：表名
	 * @param id：表记录的关键字
	 */
	private void deleteOne(String table, String id){
		String sql = "DELETE FROM " + table + " WHERE " + ID + "=\"" + id + "\";";
		db.execSQL(sql);
	}
	
	/**
	 * 教师或者学生课表中是否存在数据
	 * @param role:查看教师还是学生课表的标志
	 * @return：0则没有数据；大于0则存在课表数据
	 */
	public int hasDataInTimeTable(int role){
		int result = 0;
		String sql = "";
		
		if (role == XhuApplication.TEACHER){
			sql = "SELECT COUNT(*) FROM " + TEACHER_TIME_TABLE;
		}else if (role == XhuApplication.STUDENT) {
			sql = "SELECT COUNT(*) FROM " + STUDENT_TIME_TABLE;
		}
		cursor = db.rawQuery(sql, null);
		result = cursor.getCount();
		
		cursor.close();
		
		return result;
	}
	
	/**
	 * 获取id对应的课程信息
	 * @param role:教师或者学生
	 * @param id：关键字
	 * @return
	 */
	public StuCourseBean getOneTimeTable(int role, String id){
		StuCourseBean stuCourseBean = new StuCourseBean();
		StuCourseBean stuCourseBean1 = new StuCourseBean();
		ArrayList<StuCourseBean> courseList = new ArrayList<StuCourseBean>();
		String nid = "1" + id;
		
		if (role == XhuApplication.TEACHER){
			String sql = "SELECT *" + " FROM " + TEACHER_TIME_TABLE + " WHERE " + ID + "=\"" + nid + "\";";
			cursor = db.rawQuery(sql, null);
			if (cursor.getCount() != 0){
				cursor.moveToFirst();
				String s = cursor.getString(0).substring(1).trim();
				//取出数据
				stuCourseBean.x = Integer.parseInt(s.substring(0, 1));
				stuCourseBean.y = Integer.parseInt(s.substring(1, 2));
				stuCourseBean.name = cursor.getString(1);
				stuCourseBean.teacher = cursor.getString(2);
				stuCourseBean.site = cursor.getString(3);
				stuCourseBean.time = cursor.getString(4);
				stuCourseBean.more = cursor.getString(5);
				stuCourseBean.remark = cursor.getString(6);
			}
			cursor.close();
			
		}else if (role == XhuApplication.STUDENT) {
			String sql = "SELECT *" + " FROM " + STUDENT_TIME_TABLE + " WHERE " + ID + "=\"" + nid + "\";";
			cursor = db.rawQuery(sql, null);
			if (cursor.getCount() != 0){
				cursor.moveToFirst();
				//取出数据
				String s = cursor.getString(0).substring(1).trim();
				//取出数据
				stuCourseBean.x = Integer.parseInt(s.substring(0, 1));
				stuCourseBean.y = Integer.parseInt(s.substring(1, 2));
				stuCourseBean.name = cursor.getString(1);
				stuCourseBean.teacher = cursor.getString(2);
				stuCourseBean.site = cursor.getString(3);
				stuCourseBean.time = cursor.getString(4);
				stuCourseBean.remark = cursor.getString(5);
			}
			/*if (cursor.getCount()>0){
				cursor.moveToFirst();
				//取出数据
				String s = cursor.getString(0).substring(1).trim();
				//取出数据
				stuCourseBean.x = Integer.parseInt(s.substring(0, 1));
				stuCourseBean.y = Integer.parseInt(s.substring(1, 2));
				stuCourseBean.name = cursor.getString(1);
				stuCourseBean.teacher = cursor.getString(2);
				stuCourseBean.site = cursor.getString(3);
				stuCourseBean.time = cursor.getString(4);
				stuCourseBean.remark = cursor.getString(5);
				if(!cursor.isLast()){
					cursor.moveToNext();
					//取出数据
					String s1 = cursor.getString(0).substring(1).trim();
					//取出数据
					stuCourseBean1.x = Integer.parseInt(s1.substring(0, 1));
					stuCourseBean1.y = Integer.parseInt(s1.substring(1, 2));
					stuCourseBean1.name = cursor.getString(1);
					stuCourseBean1.teacher = cursor.getString(2);
					stuCourseBean1.site = cursor.getString(3);
					stuCourseBean1.time = cursor.getString(4);
					stuCourseBean1.remark = cursor.getString(5);
				}
			}*/
			cursor.close();
		}
		/*courseList.add(stuCourseBean);
		if(stuCourseBean1.name!=null){
			courseList.add(stuCourseBean1);
		}*/
		
		return stuCourseBean;
	}
}
